#!/usr/bin/env ruby

#------------------------------------------------------------------------------#
#                                                                              #
#  @Authors                                                                    #
#                                                                              #
#     - Almaraz Hernández, Jesús     (jesus.mah@gmail.com)                     #
#     - Campos Cantero, Pablo        (camposc.pablo@gmail.com)                 #
#     - Castelo Delgado, Tamara      (tcastelod@gmail.com)                     #
#                                                                              #
#  @Supervisor                                                                 #
#                                                                              #
#     - Santiago Montero, Rubén                                                #
#                                                                              #
#------------------------------------------------------------------------------#

################################################################################
# Required Libraries
################################################################################
require 'rubygems'
require 'sequel'
require 'json'
require 'digest/sha1'

################################################################################
# Database configuration
################################################################################
DB = Sequel.sqlite('pruebaDB.db')

################################################################################
# Database
################################################################################


#------------------------------ Students --------------------------------------#
if (!DB.table_exists?(:students))
  DB.create_table :students do
      column :id, String, :primary_key=>true
      column :name, String
      column :surname, String
      column :password, String
    end
end

def newStudent(id, name, surname, password)
  begin
  if DB[:students].filter(:id=>id).count == 1
    return true
  else
    DB[:students].insert(id, name, surname, Digest::SHA1.hexdigest(password))
    return false
  end
  rescue Exception
    raise Exception, "Insert error in Database at student's table."
  end
end

def deleteStudent(idd)
  dss = DB[:students].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dss.delete != 1)
    raise(Exception, "Delete error in Database at student's table")
  end
end

def modifyStudent(id1,password1)
  #Modifica el login y el password de un alumno con un id dado.
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:students].filter(:id=>id1).update(:password=>password1)

end

def listStudents
  dsgs = DB[:students].select(:id, :name, :surname)
  if(dsgs.count > 0)
    return JSON.pretty_generate(dsgs.all)
  end
  return nil
end
#------------------------------- Teachers -------------------------------------#
if (!DB.table_exists?(:teachers))
  DB.create_table :teachers do
      column :id, String, :primary_key=>true
      column :name, String
      column :surname, String
      column :password, String
    end
end

def newTeacher(id, name, surname, password)
  begin
  if DB[:teachers].filter(:id=>id).count == 1
    return true
  else
    DB[:teachers].insert(id, name, surname, Digest::SHA1.hexdigest(password))
    return false
  end
  rescue Exception
    raise(Exception, "Insert error in Databaseat teacher's table")
  end
end

def deleteTeacher(idd)
  dst = DB[:teachers].filter(:id=>idd)
  dsl = DB[:labs].filter(:teacher_id=>idd)
  toDelete = dsl.count
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dst.delete != 1)
	if (dsl.delete != toDelete)
    		raise(Exception, "Delete error in Database at labs table")
	end
	raise(Exception, "Delete error in Database at teachers table")
  end
end

def modifyTeacher(id1,password1)
  #Modifica el login y el password de un profesor con un id dado.
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:teachers].filter(:id=>id1).update(:password=>password1)

end

def listTeachers
  dsgt = DB[:teachers].select(:id, :name, :surname)
  if(dsgt.count > 0)
    return JSON.pretty_generate(dsgt.all)
  end
  return nil
end
#----------------------------- Administrators ---------------------------------#
if (!DB.table_exists?(:administrators))
  DB.create_table :administrators do
      String :id, :primary_key=>true
      String :name
      column :surname, String
      column :password, String
  end
  # Create an administrator
  DB[:administrators].insert(:id=>'00000000A',:name=>'administrator0',
    :surname=>'admsur0',:password=>Digest::SHA1.hexdigest('admpass0'))
end

def newAdministrator(id, name, surname, password)
  begin
    DB[:administrators].insert(id, name, surname, Digest::SHA1.hexdigest(password))
  rescue Exception
    raise(Exception, "Insert error in Database at administrator's table")
  end
end

def deleteAdministrator(idd)
  dsa = DB[:administrators].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dsa.delete != 1)
    raise(Exception, "Delete error in Database at administrator's table")
  end
end

def modifyAdministrator(id1,password1)
  #Modifica el login y el password de un admin con un id dado.
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:administrators].filter(:id=>id1).update(:password=>password1)

end

#-------------------------------- Labs ----------------------------------------#

if (!DB.table_exists?(:labs))
  DB.create_table :labs do
      primary_key(:lab_id)
      column :template_id, String   #Foreign key. References to :id on :templates table
      column :teacher_id, String    #Foreign key. References to :id on :teachers table
      column :lab_name, String
      column :lab_group, String
      column :lab_description, String


    end
end

def newLab(template_id, teacher_id, name, group, description)
  begin

  if DB[:teachers].filter(:id=>teacher_id).count == 1
      if DB[:templates].filter(:id=>template_id).count == 1
        DB[:labs].insert :template_id=>template_id, :teacher_id=>teacher_id,
          :lab_name=>name, :lab_group=>group, :lab_description=>description
      else
        raise(Exception,'There are no template with id: ' + template_id)
      end
  else
     raise(Exception,'There are no teacher with id: ' + teacher_id)
  end
  rescue Exception => prueba
    raise(Exception, "Insert error in Database at lab's table. "+prueba)
  end
end

def deleteLab(idd)
  dst = DB[:labs].filter(:lab_id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dst.delete != 1)
    raise(Exception, "Delete error in Database at lab's table")
  end
end

def modifyLab(id1,name1,description1)
  #Modifica el nombre y descripcion de un laboratorio con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:labs].filter(:lab_id=>id1).update(:lab_name=>name1, :lab_description=>description1)

end


def modifyLab2(id1,templateid1)
  #Modifica el template de un laboratorio con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:labs].filter(:lab_id=>id1).update(:template_id=>templateid1)

end


def modifyLab3(id1,teachername1,teacherid1)
  #Modifica el profesor (nombre e id propios) de un laboratorio con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:labs].filter(:lab_id=>id1).update(:teacher_name=>teachername1, :teacher_id=>teacherid1)

end

#-------------------------------- Templates -----------------------------------#
if (!DB.table_exists?(:templates))
  DB.create_table :templates do
      column :template_name, String
      #URI template description
      column :template_description, String
      #URI template
      column :value, String
      primary_key(:id)
    end
end
def newTemplate(name, description, value)
  begin
    DB[:templates].insert :template_name=>name, :template_description=>description, :value=>value
  rescue Exception
    raise(Exception, "Insert error in Database at template's table")
  end
end

def deleteTemplate(idd)
  dst = DB[:templates].filter(:id=>idd)
  dsl = DB[:labs].filter(:template_id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if(dsl.count == 0)
  	if (dst.delete != 1)
    		raise(Exception, "Delete error in Database at template's table")
  	end
	return false
  else
	return true
  end
end


def modifyTemplate(id1,name1,description1)
  #Modifica el nombre y descripcion de un template con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:templates].filter(:id=>id1).update(:template_name=>name1, :template_description=>description1)

end


################################################################################
# Queries (return JSON)
################################################################################

def authenticate(user_id,user_pass)
  dss=DB[:students].filter(:id => user_id).filter(:password => user_pass)
  dst=DB[:teachers].filter(:id => user_id).filter(:password => user_pass)
  dsa=DB[:administrators].filter(:id => user_id).filter(:password => user_pass)

  if ((dss.count+dst.count+dsa.count)>1)
	raise(Exception, "Multiple instances in diferent tables for id: "+user_id)
  end
  if (dss.count>0)
	return "Student"
  elsif (dst.count>0)
	return "Teacher"
  elsif (dsa.count>0)
	return "Administrator"
  else
	return nil
  end
end
=begin
def getStudents
  dsgs = DB[:students]
  if(dsgs.count > 0)
    return JSON.pretty_generate(dsgs.all)
  end
  return nil
end

def getTeachers
  dsgt = DB[:teachers]
  if(dsgt.count > 0)
    return JSON.pretty_generate(dsgt.all)
  end
  return nil
end

def getAdministrators
  dsga = DB[:administrators]
  if(dsga.count > 0)
    return JSON.pretty_generate(dsga.all)
  end
  return nil
end
=end

def listTemplates
  dsgtm = DB[:templates].select(:id, :template_name, :template_description)
  if(dsgtm.count > 0)
    return JSON.pretty_generate(dsgtm.all)
  end
  return nil
end

def listLabs
  templates = DB[:templates]
  dsgl=templates.join(:labs, :template_id=>:id).join(:teachers, :id=>:teacher_id).select(:lab_name, :lab_group, :lab_description, :template_description, :name, :surname)
  if(dsgl.count > 0)
    return JSON.pretty_generate(dsgl.all)
  end
  return nil
end

def listLabsID(teacherID)
  templates = DB[:templates]
  dsglID=templates.join(:labs, :template_id=>:id).join(:teachers, :id=>:teacher_id).filter(:teacher_id=>teacherID).select(:lab_id, :lab_name, :lab_group, :lab_description, :template_description, :name, :surname)
  if(dsglID.count > 0)
    return JSON.pretty_generate(dsglID.all)
  end
  return nil
end

def listLabsDel()
  templates = DB[:templates]
  dsglID=templates.join(:labs, :template_id=>:id).join(:teachers, :id=>:teacher_id).select(:lab_id, :lab_name, :lab_group, :lab_description, :template_description, :name, :surname)
  if(dsglID.count > 0)
    return JSON.pretty_generate(dsglID.all)
  end
  return nil
end

=begin
  newStudent('93242232S','Jesus','Almaraz','Jesuspass')
  newStudent('51992157P','Pablo','Campos','Pablopass')
  newStudent('50232837C','Tamara','Castelo','Tamarapass')
  newStudent('11834932K','Javier','Becares','Becariopass')
  newStudent('49234201N','Enrique','Ortiz','Ortizpass')
  newStudent('02193835C','Lara','Quijano','Lariospass')
  newStudent('22452085Y','Sergio','Agüero','Kunpass')
  newStudent('40293771R','Maria Jose','Solana','MJpass')
  newStudent('70023654A','Alejandro','Gomez','Alexpass')
  newStudent('98978302B','Francisco','Calvo','Pacopass')
  newStudent('66297137P','Jose Felix','Roldan','Nenepass')
  newStudent('04382119X','Jorge','Sanz','Jorgepass')

  newTeacher('86543678T','Rafael','del Vado','Rafaelpass')
  newTeacher('11393423B','Alfredo','Bautista','Alfredopass')
  newTeacher('20764788V','Luis','Vazquez','Luispass')
  newTeacher('36064488T','Ruben','Santiago','Rubenpass')
  newTeacher('44666696M','Matilde','Santos','Matildepass')
  newTeacher('57243101A','Purificacion','Arenas','Puripass')
  newTeacher('60640432E','Elvira','Albert','Elvirapass')
  newTeacher('70729013G','Guadalupe','Miñana','Antoniopass')
  newTeacher('80514726C','Rafael','Caballero','Bertoldopass')
  newTeacher('91478400Y','Yolanda','García','Yolipass')


  newTemplate('Template0','memory: 512 <br>cpu: 2<br>disk: {<br>type: fs<br>format: ext3<br>size: 1024<br>}<br>name:t0','URI')
  newTemplate('Template1','memory: 128 <br>cpu: 1<br>disk: {<br>type: ntfs<br>format: ext2<br>size: 512<br>}<br>name:t1','URI')
  newTemplate('Template2','memory: 2048<br>cpu: 3<br>disk: {<br>type: FAT32<br>format: ext1<br>size: 8192<br>}<br>name:t2','URI')
  newTemplate('Template3','memory: 1024<br>cpu: 2<br>disk: {<br>type: fs<br>format: extl3<br>size: 2048<br>}<br>name:t3','URI')

  newLab(4,'11393423B','LSO','GroupA','Laboratorio de Sistemas Operativos')
  newLab(2,'20764788V','AN','GroupA','Laboratorio de Análisis Numérico')
  newLab(3,'44666696M','MSS','GroupA','Laboratorio de Modelado y Simulación de Sistemas')
  newLab(4,'60640432E','LP3','GroupC','Laboratorio de Programación 3')
  newLab(1,'36064488T','LFC','GroupA','Laboratorio de Fundamentos de Computadores')
  newLab(3,'57243101A','LP2','GroupA','Laboratorio de Programación 2')
  newLab(3,'91478400Y','BDSI','GroupA','Laboratorio de Bases de Datos')
  newLab(1,'11393423B','LEC','GroupA','Laboratorio de Estructuras de Computadores')
  newLab(1,'36064488T','LEC','GroupB','Laboratorio de Estructuras de Computadores')
  newLab(2,'86543678T','EDI','GroupA','Laboratorio de Estructuras y Datos de la Información')
  newLab(2,'86543678T','EDI','GroupB','Laboratorio de Estructuras y Datos de la Información')
  newLab(4,'70729013G','LTC','GroupA','Laboratorio de Tecnología de Computadores')

puts getStudents
puts getTeachers
puts getAdministrators
puts getTemplates
puts getLabs

 labs = DB[:labs]
  join1=labs.join(:templates, :id=>:template_id).select(:teacher_id,:lab_name, :lab_group, :lab_description, :template_description)
  join2=labs.join(:teachers, :id=>:teacher_id).select(:teacher_id)
puts JSON.pretty_generate(join1.all)
puts JSON.pretty_generate(join2.all)
=end
#templates = DB[:templates]
  #join1=templates.join(:labs, :template_id=>:id).join(:teachers, :id=>:teacher_id)
  #join2=labs.join(:teachers, :id=>:teacher_id).select(:teacher_id,:name,:surname)
  #dsgl=join1.join(join2, :teacher_id=>:teacher_id).select(:lab_description, :name, :surname)
#puts JSON.pretty_generate(join1.all)
#puts JSON.pretty_generate(join2.all)
#puts JSON.pretty_generate(dsgl.all)
#puts listStudents()
#newStudent("04849079N","Jesus","Almaraz","04849079N")